package batch.pmgm;

import java.util.*;
import java.sql.*;

import java.util.Date;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.*;

import batch.common.db.*;
import batch.common.report.*;

public class PmgmSt_active {
    private final String TODAY  = DateFormatUtils.format(new Date(), "yyyyMMdd");
    
    private final String MAILSUBJECT = "PmgmSt_active Error! (" + TODAY + ")"; 

    private String batch_date;

    /*******************************************************************************************************/
    /* Name : PmgmSt_active
    /* Arguments : none
    /* Return : none
    /* Description : creator
    /*******************************************************************************************************/
    public PmgmSt_active(){
        batch_date = TODAY;
    }

    /*******************************************************************************************************/
    /* Name : PmgmSt_active
    /* Arguments : String(--yyyymmdd--))
    /* Return : none
    /* Description : creator
    /*******************************************************************************************************/
    public PmgmSt_active(String date){
        batch_date = date;
    }

    /*******************************************************************************************************/
    /* Name : main
    /* Arguments : String(--yyyymmdd--)
    /*******************************************************************************************************/
    public static void main(String[] args)
    {

        PmgmSt_active pmgm_active = null;

        if ( args==null || args.length==0 ) {
            pmgm_active = new PmgmSt_active();
        } else {
            pmgm_active = new PmgmSt_active(args[0]);
        }

        Logger.getLogger("pmgm.pmgmst_active").info(" ( * ) Start.");
        pmgm_active.start();
        Logger.getLogger("pmgm.pmgmst_active").info(" ( * ) End.");
    }
    /*******************************************************************************************************/

    /*******************************************************************************************************/
    /* Name : start
    /* Arguments : none
    /* Return : none
    /* Description : execute each statistics function
    /*******************************************************************************************************/
    public void start()
    {
        Connection conn = null;
        Statement stmt = null;

        try {
            // get connection
            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(false);
            stmt = conn.createStatement();

            //get history table names
            Logger.getLogger("pmgm.pmgmst_active").info(" ( * ) getting point history table start.");
            Vector vTab_list = selectHistoryTab(stmt);
            Logger.getLogger("pmgm.pmgmst_active").info(" ( * ) success.");

            //insert into G_PMGM_ST_ACTIVE
            Logger.getLogger("pmgm.pmgmst_active").info(" ( * ) inserting activity statistics start.");
            for ( int i=0; i<vTab_list.size(); i++ ) {
                int nCnt = insertPmgm_act((String)vTab_list.elementAt(i), stmt);
                Logger.getLogger("pmgm.pmgmst_active").info(" ( * ) " + vTab_list.elementAt(i) + ": " + nCnt + " inserted.");
            }
            Logger.getLogger("pmgm.pmgmst_active").info(" ( * ) success.");

            // final commit
            conn.commit();
        } catch(Exception e) {
            if (conn != null) try { conn.rollback(); } catch (Exception ex) { ex.printStackTrace(); }
            
            e.printStackTrace();

            Logger.getLogger("pmgm.pmgmst_active").info(" ( ! ) " + e.toString());

            StackTraceElement[] stElement = e.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("pmgm.pmgmst_active").info(" ( ! ) " + stElement[p].toString());
            }

            ReportError.byEmail(MAILSUBJECT, e.toString());
        } finally {
            if (stmt != null) try { stmt.close(); } catch (Exception e) { e.printStackTrace(); }
            if (conn != null) try { conn.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }

    /*******************************************************************************************************/
    /* Name : selectHistoryTab
    /* Arguments : Statement
    /* Return : Vector vTab_list (--table name array--)
    /* Description : select point history table name from USER_OBJECTS
    /*******************************************************************************************************/
    public Vector selectHistoryTab(Statement stmt) throws Exception {
        Vector vTab_list = new Vector();
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT OBJECT_NAME \n");
        sql.append("   FROM USER_OBJECTS WHERE OBJECT_NAME LIKE '%POINT_HISTORY' AND \n");
        sql.append("                OBJECT_NAME <> 'G_LTA_POINT_HISTORY' AND \n");
        sql.append("              OBJECT_TYPE = 'TABLE' \n");

        ResultSet rs = null;

        try{
            rs = stmt.executeQuery(sql.toString());
            while(rs.next()){
                vTab_list.addElement(rs.getString(1));
            }
        }
        finally {
            if (rs != null) try { rs.close(); } catch (Exception e) {}
        }

        return vTab_list;
    }

    /*******************************************************************************************************/
    /* Name : insertPmgm_act
    /* Arguments : String table_nm, Statement
    /* Return : int
    /* Description : G_PMGM_ST_ACTIVE
    /*******************************************************************************************************/
    public int insertPmgm_act(String table_nm, Statement stmt) throws Exception {
        StringBuffer sql = new StringBuffer();
        sql.append(" INSERT INTO G_PMGM_ST_ACTIVE (SEQ_ID, MST_CD, SITE_CD, CREATE_YM, CNT_NUM) \n");
        sql.append(" ( SELECT SEQ_G_PMGM_ST_MB.NEXTVAL, D.* FROM \n");
        sql.append("    ( SELECT C.MST_CD, '"+table_nm.substring(2,5).toLowerCase()+"', '"+batch_date.substring(0,6)+"', COUNT(*) \n");
        sql.append("      FROM G_PMGM_MST_SUB C,  \n");
        sql.append("          (SELECT DISTINCT A.ID, SUBSTR(MEMBERGBN,1,5) MST, SUBSTR(MEMBERGBN,6,8) SUB \n");
        sql.append("           FROM G_MB_MEMBER A, "+table_nm+" B \n");
        sql.append("           WHERE A.ID = B.USER_ID \n");
        sql.append("                 AND  B.CREATED_YMDHMS LIKE '"+batch_date.substring(0,6)+"%'  \n");
        sql.append("                 AND  B.POINT_CD IN ('00103','00104') ) A \n");
        sql.append("      WHERE  C.MST_CD = A.MST AND C.SUB_CD = A.SUB AND C.SITE_CD='" +table_nm.substring(2,5).toLowerCase()+ "'\n");
        sql.append("      GROUP BY C.MST_CD ) D ) ");

        return stmt.executeUpdate(sql.toString());
    }
}
